[转帖]DATA GURAD物理STANDBY建立_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 3670 | 回复: 0

主题： [转帖]DATA GURAD物理STANDBY建立

kim

注册用户

等级：中校
经验：1729
发帖：222
精华：0
注册：2011-7-21
状态：离线
发送短消息息给kim

加好友发送短消息息给kim

发消息

发表于：

2011-8-22 13:34:41 | [全部帖] [楼主帖]

楼主

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

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

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

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

SQL> SELECT DBID, NAME, LOG_MODE, FORCE_LOGGING FROM V$DATABASE;
DBID NAME LOG_MODE FOR
---------- --------- ------------ ---
2270762593 TESTDATA NOARCHIVELOG NO
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=/data/oradata/testdata/archive';
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1 = ENABLE;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_START = TRUE SCOPE = SPFILE;
System altered.
SQL> 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.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> SELECT DBID, NAME, LOG_MODE, FORCE_LOGGING FROM V$DATABASE;
DBID NAME LOG_MODE FOR
---------- --------- ------------ ---
2270762593 TESTDATA ARCHIVELOG YES

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

SQL> 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数据库所在服务器的相同目录下：

SQL> 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：

SQL> STARTUP
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.
Database opened.
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/data/oradata/control01.ctl';
Database altered.
SQL> CREATE PFILE = '/data/oradata/inittestdata.ora' FROM SPFILE;
File created.

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

SQL> 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
SQL>

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

[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)
)
)

在从库中建立SPFILE，然后启动从库：

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

SQL*Plus: Release 9.2.0.4.0 - Production on 星期二 2月 27 14:33:38 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> CREATE SPFILE FROM PFILE = '/opt/ora9/admin/testdata/pfile/inittestdata.ora';
File created.
SQL> 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.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.

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

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=TESTDATA_STANDBY';
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE;
System altered.

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

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.

最后，验证一下物理STANDBY是否已经成功建立：

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
Session altered.
SQL> 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
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
SQL> 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.

登陆从库检查：

SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APP
---------- ---
1822 YES
1823 YES

至此物理STANDBY已经成功建立。