搭建 physical standby——copy数据文件
一、DG环境说明
数据库版本：11.2.0.1.0
操作系统：redhat linux 5
主库：192.168.88.3 sharon
备库：192.168.88.2 sharon
使用静态IP，要确保IP是固定的，不要用DHCP来分配！
二、具体步骤
1. 启动force logging
SQL> ALTER DATABASE FORCE LOGGING;
检查：
SQL> select force_logging from v$database;
FORCE_LOG
---------
YES
2. 启动归档模式
SQL> archive log list;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter system setlog_archive_dest_1='location=/u01/arch/' scope=spfile;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch/
Oldest online log sequence 91
Next log sequence to archive 92
Current log sequence 92
3. 检查口令文件
[oracle@sharon ~]$ cd $ORACLE_HOME/dbs
[oracle@sharon dbs]$ ll
total 44
-rw-rw---- 1 oracle oinstall 1544 Apr 21 12:35 hc_sharon.dat
-rw-r----- 1 oracle oinstall12920 May 3 2001 initdw.ora
-rw-r----- 1 oracle oinstall 8385 Sep 11 1998 init.ora
-rw-rw---- 1 oracle oinstall 24 Apr 21 12:43 lkSHARON
-rw-r----- 1 oracleoinstall 1536 Apr 21 12:46 orapwsharon
-rw-r----- 1 oracle oinstall 3584 Apr 22 09:01 spfilesharon.ora
如果不存在，手工创建，使用orapwd命令。 这个命令怎么用，就不多做说明，不清楚的google一下：
[oracle@dg1 /]$ orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/ orapwdave password=admin entries=30
4. 在主备库分别创建listener，并启动
用netca命令来调用图形化界面创建！
5.在主备库添加Oracle Net Service（tnsnames.ora），配置静态监听
使用netmgr命令来调用图形化界面创建。
配置静态监听：
注意大小写。有一次做的时候，实例名是大写的，配置的动态监听中sid 写成了小写，导致rman duplicate的时候连不上！
【说明】
*4、5两步可以一起用netmgr来做，一次性搞定，比较方便！
*可以直接修改listener.ora,tnsnames.ora来配置监听和oraclenet service，但是不建议这么做，很容易出错，多一个空格少一个空格就可能导致服务无法用，还是建议用工具来配置！
*配置静态监听默认端口号为1521，若冲突配置其他的端口号需要手工注册以下。
[oracle@sharon admin]$ netmgr
[oracle@sharon admin]$ cat tnsnames.ora
# tnsnames.ora NetworkConfiguration File:/u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generatedby Oracle configuration tools.
--注意我这里的提示，它是使用工具生成的。
SHARON_PD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.88.3)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sharon)
)
)
SHARON_ST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.88.2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sharon)
)
)
[oracle@sharon admin]$ cat listener.ora
# listener.ora NetworkConfiguration File:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generatedby Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = sharon)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbs)
(SID_NAME = sharon)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.88.3)(PORT = 1521))
)
)
配置好重启下监听：
[oracle@sharon admin]$ lsnrctl stop
[oracle@sharon admin]$ lsnrctl start
[oracle@sharon admin]$ lsnrctl status
LSNRCTL for Linux: Version11.2.0.1.0 - Production on 24-APR-2013 10:40:28
Copyright (c) 1991, 2009,Oracle. All rights reserved.
Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.88.2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version11.2.0.1.0 - Production
Start Date 24-APR-2013 10:40:03
Uptime 0 days 0 hr. 0 min. 26 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/sharon/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.88.2)(PORT=1521)))
Services Summary...
Service "sharon" has 1instance(s).
Instance "sharon", status UNKNOWN,has 1 handler(s) for this service...
The command completedsuccessfully
*状态为UNKNOWN就代表是静态监听
tnsping下各服务，看是否通
[oracle@sharon admin]$ tnsping sharon_pd
TNS Ping Utility for Linux: Version 11.2.0.1.0- Production on 26-APR-2013 09:52:51
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION =(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.88.3)(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME = sharon_pd)))
OK (0 msec)
[oracle@sharon admin]$ tnsping sharon_st
TNS Ping Utility for Linux: Version 11.2.0.1.0- Production on 26-APR-2013 09:52:51
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION =(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.88.2)(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME = sharon_st)))
OK (0 msec)
6.在主库创建standbycontrol file
这里一定要把数据库启动到mount状态来创建：
SQL> alter database create standby controlfile as '/u01/control01.ctl';
*数据库实例是会不停地更新控制文件的，因为如果我们不关闭实例，直接把数据文件copy到备库，这个操作上是没有问题的，但是当我们打开数据库的时候，一定会提示我们如下的错误：
ORA-10458: standbydatabase requires recovery
ORA-01194: file 1 needsmore recovery to be consistent
ORA-01110: data file 1:'/u01/app/oracle/oradata/dave/system01.dbf'
我们说的copy 方法，就是直接把数据文件，online redo 文件直接copy到备库。
主库一直保持mount 状态！
7.在主库创建pfile文件并修改pfile内容
SQL> create pfile from spfile;
在pfile添加如下内容：
#add for primary dg
*.db_unique_name='sharon_pd'
*.log_archive_config='dg_config=(sharon_pd,sharon_st)'
*.log_archive_dest_1='location=/u01/archlog valid_for=(all_logfiles,all_roles) db_unique_name=sharon_pd'
*.log_archive_dest_2='service=sharon_st reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=sharon_st'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='sharon_st'
*.fal_client=’sharon_pd’ –10g 要写！11g不需要写！
注意：
（1）在oracle 11g中，废弃了fal_client 参数，也就是说不用配置。
（2）log_archive_dest_n 这个参数中的Oracle Net Service名首位是一样的，前面写哪个，最后的db_unique_name就写哪个，不要搞错了。
（3）如果主备库的路径不同，修改在主库的参数文件里添加如下2个参数：
*.log_file_name_convert='/u02/oradata/orcl/','/u03/oradata/orcl/'
*.db_file_name_convert='/u02/oradata/orcl/','/u03/oradata/orcl/'
--用修改过的pfile重新生成spfile：
Shutdown 再创建
用pfile启动到mount
SQL>startup mount pfile=’$ORACLE_HOME/dbs/initsharon.ora’;
SQL> create spfile from pfile;
8.在备库创建相关的目录
因为我们的备库没有创建实例，所以是没有相关的目录的，我们需要参考主库的位置来创建。
以免遗漏可以看主库的pfile来创建！
[oracle@sharon oracle]$ pwd
/u01/app/oracle
[oracle@sharon oracle]$ ls
admin checkpoints diag fast_recovery_area oradata product
--FRA:
[oracle@sharon sharon]$ pwd
/u01/app/oracle/fast_recovery_area/sharon
--DATAFILE:
[oracle@sharon sharon]$ pwd
/u01/app/oracle/oradata/sharon
[oracle@sharon sharon]$ pwd
/u01/app/oracle/admin/Sharon
[oracle@sharon sharon]$ mkdir –p /u01/app/oracle/oradata/sharon
[oracle@sharon sharon]$ mkdir –p /u01/app/oracle/fast_recovery_area/sharon
[oracle@sharon sharon]$ mkdir –p /u01/app/oracle/admin/sharon/adump
[oracle@sharon sharon]$ mkdir –p /u01/app/oracle/admin/sharon/dpdump
9. 将主库的参数文件copy到备库并修改
[oracle@sharon dbs]$ scp initsharon.ora 192.168.88.2:/u01/app/oracle/product/11.2.0/db_1/dbs
参数文件，我们在主库的pfile中已经修改，我们这里只需要该2个参数即可：
#add for standby dg
*.db_unique_name='sharon_st'
*.log_archive_config='dg_config=(sharon_pd,sharon_st)'
*.log_archive_dest_1='location=/u01/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=sharon_st'
*.log_archive_dest_2='service=sharon_pd reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=sharon_pd'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='sharon_pd'
*.fal_client='sharon_st' --10g要写！11g不用写！
10.将主库的密码文件，控制文件，数据文件，日志文件copy到备库
--口令文件：
[oracle@sharon dbs]$ scp orapwsharon 192.168.88.2:/u01/app/oracle/product/11.2.0/db_1/dbs
--控制文件：
--从备库取：注意修改了控制文件的名称：
[oracle@sharon sharon]$ scp 192.168.88.3:/u01/control01.ctl /u01/app/oracle/oradata/sharon/control01.ctl
[oracle@sharon sharon]$ scp 192.168.88.3:/u01/control01.ctl /u01/app/oracle/fast_recovery_area/sharon/control02.ctl
注意：11g的控制文件在不同的目录下面，control02.ctl在FRA中！
--数据文件
[oracle@sharon sharon]$ scp 192.168.88.3:/u01/app/oracle/oradata/sharon/*.dbf /u01/app/oracle/oradata/sharon/
11.启动备库并应用applyservice
[oracle@sharon sharon]$sqlplus / as sysdba
--11g直接startup
SQL> startup
SQL> select open_modefrom v$database;
OPEN_MODE
--------------------
READONLY
--10g：
SQL> startup nomount;
SQL>alter database mount standby database ;
SQL>alter database recover managed standby database disconnect from session;
12. 将主库启动到open 状态并检查DG 配置是否有错误
我们在创建standby 控制文件的是，把主库启动到mount状态的。
SQL> alter database open;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
--查询DG 状态：
SQL> col error for a10
SQL> col dest_name for a20
SQL> select DEST_NAME,STATUS,PROCESS,ERROR,TRANSMIT_MODE from v$archive_dest WHERE TARGET='STANDBY';
DEST_NAME STATUS PROCESS ERROR TRANSMIT_MOD
-------------------- --------- ---------- ---------- ------------
LOG_ARCHIVE_DEST_2 VALID LGWR ASYNCHRONOUS
如果DG 配置的有问题，这里会显示INVALID，并且error 会提示具体的错误原因，也可以查看alert log 来确定原因。
13. 在主备库添加online redo log 和standby redo log
--主库添加standby redo log：大小和online redo 相同，比online redo group 多一组。
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4('/u01/app/oracle/sharon/redo04.log') size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5('/u01/app/oracle/sharon/redo05.log') size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6('/u01/app/oracle/sharon/redo06.log') size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7('/u01/app/oracle/sharon/redo07.log') size 50M;
--验证：
SQL> col member for a50
SQL> select group#,type, member from v$logfile;
--备库执行
SQL> col member for a50
SQL> select group#,type, member from v$logfile;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4('/u01/app/oracle/oradata/sharon/redo04.log') size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5('/u01/app/oracle/oradata/sharon/redo05.log') size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6('/u01/app/oracle/oradata/sharon/redo06.log') size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7('/u01/app/oracle/oradata/sharon/redo07.log') size 50M;
--验证：
SQL> col member for a50
SQL> select group#,type, member from v$logfile;
--Oracle 11g的物理standby 备库是启动到read-only状态的
SQL> alter database recover managed standbydatabase disconnect from session;
Database altered.
11g
SQL> select open_modefrom v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
测试：
--主库：
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
23
SQL> alter system switch logfile;
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
24
--查询DG 状态：
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
24
SQL> col error for a10
SQL> col dest_name for a20
SQL>select DEST_NAME,STATUS,PROCESS,ERROR,TRANSMIT_MODEfrom v$archive_dest WHERE TARGET='STANDBY';
DEST_NAME STATUS PROCESS ERROR TRANSMIT_MOD TARGET
----------------------------- ---------- ---------- ------------ -------
LOG_ARCHIVE_DEST_2 VALID LGWR ASYNCHRONOUS STANDBY
注意：
当监听不在1521端口时，就需要设置local_listener参数。
将监听的信息添加到tnsnames.ora 文件中。pmon在动态注册监听时要从tnsnames.ora中读取信息。
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =TCP)(HOST = IP)(PORT = 1522))
)
然后以sys用戶运行：
SQL> alter system setlocal_listener=listener;
SQL> alter system register;
或者：
SQL> alter system setLOCAL_LISTENER='(ADDRESS = (PROTOCOL = TCP)(HOST = IP)(PORT = 1522))';
SQL> alter system register;
8544
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
