ORACLE COPY数据文件方式搭建DG -- cnDBA.cn

1.环境说明

vbox 准备两个虚拟机，Linux 环境 OracleLinux x86.64-dvd，一个虚拟机已经搭建好库，一个虚拟机装好数据库软件，两个虚拟机要能相互ping 通，实例名都是orcl(实例名区分大小写)。

主库数据库版本


SQL> select * from v$version; 

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE	11.2.0.4.0	Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

主库:192.168.56.64

备库:192.168.56.70

2 主库设置为 force logging 模式

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size		 2259840 bytes
Variable Size		 889193600 bytes
Database Buffers	 146800640 bytes
Redo Buffers		 5632000 bytes
Database mounted.
SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database; 

FOR
---
YES

--取消force logging命令

SQL> alter database no force logging; 
Database altered.
SQL> select force_logging from v$database; 
FOR
---
NO

3 修改主库为归档模式

--创建归档目录并赋权限

[root@localhost ~]# mkdir /u01/archive
[root@localhost ~]# chown oracle:oinstall /u01/archive

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size		 2259840 bytes
Variable Size		 889193600 bytes
Database Buffers	 146800640 bytes
Redo Buffers		 5632000 bytes
Database mounted.
SQL> archive log list
Database log mode	 No Archive Mode
Automatic archival	 Disabled
Archive destination	 USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 5
Current log sequence	 7
SQL> alter database archivelog;
Database altered.
SQL> alter system set log_archive_dest_1='location=/u01/archive/' scope=spfile;
System altered.

SQL> archive log list
Database log mode	 Archive Mode
Automatic archival	 Enabled
Archive destination	 USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence	 7

4 添加主库的standby redo log

主库添加 standby redo log：大小和 online redo 相同，比 online redo group 多一组。

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/orcl/redo04.log') size 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/orcl/redo05.log') size 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/orcl/redo06.log') size 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/orcl/redo07.log') size 50M;

Database altered.

SQL> select GROUP#,SEQUENCE#,BYTES/1024/1024,STATUS from v$log;

 GROUP# SEQUENCE# BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------------
	 1	 7		 50 CURRENT
	 3	 6		 50 INACTIVE
	 2	 5		 50 INACTIVE

SQL> col member for a50 
SQL> select group#,type, member from v$logfile;

 GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------
	 3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log
	 2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log
	 1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log
	 4 STANDBY /u01/app/oracle/oradata/orcl/redo04.log
	 5 STANDBY /u01/app/oracle/oradata/orcl/redo05.log
	 6 STANDBY /u01/app/oracle/oradata/orcl/redo06.log
	 7 STANDBY /u01/app/oracle/oradata/orcl/redo07.log

7 rows selected.

5 在主备库分别创建Listener 并配置静态注册
使用 netca 命令创建监听，netmgr 命令配置静态注册

--主库192.168.56.64监听

[oracle@localhost admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = orcl)
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
 (SID_NAME = orcl)
 )
 )
LISTENER =
 (DESCRIPTION_LIST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
 )
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
 )
 )
ADR_BASE_LISTENER = /u01/app/oracle

--备库192.168.56.70监听

[oracle@localhost admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = orcl)
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
 (SID_NAME = orcl)
 )
 )

LISTENER =
 (DESCRIPTION_LIST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
 )
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
 )
 )

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER 部分的内容就是静态注册，如果没有该参数，而且 Data Guard 启动顺序又不正确，那么在

主库可能会报 PING[ARC1]: Heartbeat failed to connect to standby 'orcl_st'. Error is 12512. 错误，导致归档无法完成

6 在主备库添加 Oracle Net Service

特别说明一下在备库没有安装数据库的时候/u01/app/oracle/product/11.2.0/db_1/network/admin 目录下没有tnsnames.ora 文件

此时需要手工建netmgr 创建

[oracle@localhost network]$ cd admin
[oracle@localhost admin]$ ls
listener17040310PM5421.bak  shrept.lst                tnsnames17040310PM5421.bak
listener17040311PM0939.bak  sqlnet17040310PM5421.bak  tnsnames17040311PM0939.bak
listener1704038PM5302.bak   sqlnet17040311PM0939.bak  tnsnames.ora
listener.ora                sqlnet1704038PM5302.bak
samples                     sqlnet.ora
[oracle@localhost admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.64)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
--添加一下内容
ORCL_PD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.64)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
ORCL_ST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.70)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

配置完成后，使用 tnsping 命令效验：

--先检查防火墙有没有关闭，如果防火墙开启，以下两种方法操作：

1）关闭防火墙，

2）开发1521 端口。

--开放1521端口方法

vi /etc/sysconfig/iptables

--添加一行

-A INPUT -m state --state NEW -m tcp -p tcp --dport 1521 -j ACCEPT

重启防火墙

[root@localhost ~]# /etc/init.d/iptables restart iptables: Setting chains to policy ACCEPT: filter [ OK ] iptables: Flushing firewall rules: [ OK ] iptables: Unloading modules: [ OK ] iptables: Applying firewall rules: [ OK ] [root@localhost ~]# 



--开始校验

[oracle@localhost admin]$ tnsping orcl_pd

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 25-APR-2017 10:18:10

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.64)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@localhost admin]$ tnsping orcl_st

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 25-APR-2017 10:18:12

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.70)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (100 msec)

7 在备库创建相关的目录

因为我们的备库没有创建实例，所以是没有相关的目录的，我们需要参考主库的位置来创建。

[oracle@localhost ~]$ cd /u01/app/oracle
[oracle@localhost oracle]$ ls
admin        checkpoints  fast_recovery_area  oradiag_oracle
cfgtoollogs  diag         oradata             product
[oracle@localhost oracle]$

--FRA目录

[oracle@localhost orcl]$ pwd
/u01/app/oracle/fast_recovery_area/orcl

--DATAFILE

[oracle@localhost orcl]$ pwd
/u01/app/oracle/oradata/orcl

[oracle@localhost orcl]$ pwd
/u01/app/oracle/admin/orcl

--特别说明，在pfile 参数文件提到的目录都要建，

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump' adump 目录不建的话会提示以下错误：

SQL> conn /as sysdba
ERROR:
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
ORA-01075: you are currently logged on

8 在主库创建 standby control file

把数据库启动到 mount 状态来创建：

SQL> alter database create standby controlfile as '/u01/control01.ctl';

如果不是 mount，不能保证数据的一致性，在备库启动的时候，会报如下错误：

ORA-10258: standby database requires recovery 
ORA-01192: file 1 needs more recovery to be consistent 
ORA-01110: data file 1: '/u01/app/oracle/oradata/dave/system01.db

9 在主库创建 pfile 文件并修改 pfile 内容

SQL> create pfile from spfile;

File created.

修改 pfile

[oracle@localhost dbs]$ ls
hc_orcl.dat  initorcl.ora  lkORCL_ST  snapcf_orcl.f
init.ora     lkORCL_PD     orapworcl  spfileorcl.ora
[oracle@localhost dbs]$ cat initorcl.ora 
orcl.__db_cache_size=146800640
orcl.__java_pool_size=4194304
orcl.__large_pool_size=587202560
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=25165824
orcl.__sga_target=1023410176
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=272629760
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=1048576000
*.open_cursors=300
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1655
*.undo_tablespace='UNDOTBS1'
--添加以下内容
*.db_unique_name=orcl_pd 
*.log_archive_config='dg_config=(orcl_pd,orcl_st)' 
*.log_archive_dest_1='location=/u01/archive valid_for=(all_logfiles,all_roles) db_unique_name=orcl_pd' 
*.log_archive_dest_2='service=orcl_st valid_for=(online_logfiles,primary_role) db_unique_name=orcl_st' 
*.log_archive_dest_state_1=enable 
*.log_archive_dest_state_2=enable 
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE 
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc 
*.standby_file_management='auto' 
*.fal_server='orcl_st'

注意：

（1）在 oracle 11g 中，废弃了 fal_client 参数，也就是说不用配置。

（2）log_archive_dest_n 这个参数中的Oracle Net Service名首位是一样的，前面写哪个，最后的db_unique_name

就写哪个，不要搞错了。

如果主备库的路径不同，修改在主库的参数文件里添加如下 2 个参数：

*.log_file_name_convert='/u02/oradata/orcl/','/u03/oradata/orcl/'

*.db_file_name_convert='/u02/oradata/orcl/','/u03/oradata/orcl/'

前面的是旧的路径，后面的是新的路径

用pfile启动主库，并创建spfile

SQL> startup  nomount
ORA-00845: MEMORY_TARGET not supported on this system
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora'
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size		    2259840 bytes
Variable Size		  889193600 bytes
Database Buffers	  146800640 bytes
Redo Buffers		    5632000 bytes
SQL> create spfile from pfile; 
SP2-0734: unknown command beginning "create sp..." - rest of line ignored.
SQL> create spfile from pfile;

File created.

10 将主库的口令文件，参数文件 copy 到备库并修改

[oracle@localhost dbs]$ scp initorcl.ora 192.168.56.71:/u01/app/oracle/product/11.2.0/db_1/dbs
oracle@192.168.56.68's password: 
initorcl.ora 100% 1672 1.6KB/s 00:00 
[oracle@localhost dbs]$ scp orapworcl 192.168.56.71:/u01/app/oracle/product/11.2.0/db_1/dbs
oracle@192.168.56.68's password: 
orapworcl 100% 1536 1.5KB/s 00:00

如果不存在，手工创建，使用 orapwd 命令。

[oracle@dg1 /]$ orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwdave password=admin entries=30

注意：这里的实例名区分大小写。

--修改备库pfile

[oracle@localhost dbs]$ cat initorcl.ora 
orcl.__db_cache_size=146800640
orcl.__java_pool_size=4194304
orcl.__large_pool_size=587202560
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=25165824
orcl.__sga_target=1023410176
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=272629760
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=1048576000
*.open_cursors=300
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1655
*.undo_tablespace='UNDOTBS1'
--添加以下内容
*.db_unique_name=orcl_st 
*.log_archive_config='dg_config=(orcl_pd,orcl_st)' 
*.log_archive_dest_1='location=/u01/archive valid_for=(all_logfiles,all_roles) db_unique_name=orcl_st' 
*.log_archive_dest_2='service=orcl_pd valid_for=(online_logfiles,primary_role) db_unique_name=orcl_pd' 
*.log_archive_dest_state_1=enable 
*.log_archive_dest_state_2=enable 
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE 
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc 
*.standby_file_management='auto' 
*.fal_server='orcl_pd'

11 将主库的控制文件，数据文件，日志文件 copy 到备库

--控制文件

--主库传送到备库

[oracle@localhost u01]$ scp control01.ctl 192.168.56.70:/u01/app/oracle/oradata/orcl
oracle@192.168.56.70's password: 
control01.ctl                                   100% 9712KB   9.5MB/s   00:00

--将主库控制文件冲命名到/u01/app/oracle/fast_recovery_area/orcl

[oracle@localhost orcl]$ cp control01.ctl /u01/app/oracle/fast_recovery_area/orcl/control02.ctl

--数据文件，日志文件

[oracle@localhost orcl]$ scp *.dbf 192.168.56.70:/u01/app/oracle/oradata/orcl
[oracle@localhost orcl]$ scp *.log 192.168.56.70:/u01/app/oracle/oradata/orcl

12 启动备库

[oracle@localhost orcl]$ sqlplus / as sysdba  
  
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 22 15:58:40 2016  
  
Copyright (c) 1982, 2013, Oracle.  All rights reserved.  
  
Connected to an idle instance.  
  
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora'  
ORACLE instance started.  
  
Total System Global Area 1653518336 bytes  
Fixed Size          2253784 bytes  
Variable Size        1006636072 bytes  
Database Buffers      637534208 bytes  
Redo Buffers            7094272 bytes  
SQL> create spfile from pfile; 
File created. 
   

    http://www.cndba.cn/leo1990/article/1888
   

 SQL> alter database mount;      
Database altered.      
SQL> alter database open;      D
atabase altered.      S
QL> select open_mode from v$database;      
OPEN_MODE   
--------------------   
READ ONLY

13 将主库启动到 open 状态并检查 DG 配置是否有错误

[oracle@localhost orcl]$ sqlplus / as sysdba  
  
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 22 17:59:40 2016  
  
Copyright (c) 1982, 2013, Oracle.  All rights reserved.  
  
Connected to an idle instance.  
  
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora'  
ORACLE instance started.  
  
Total System Global Area 1653518336 bytes  
Fixed Size          2253784 bytes  
Variable Size        1006636072 bytes  
Database Buffers      637534208 bytes  
Redo Buffers            7094272 bytes  
SQL> alter database mount;  
  
Database altered.  
  
SQL> alter database open;  
  
Database altered.  
  
SQL> select open_mode from v$database;  
  
OPEN_MODE  
--------------------  
READ WRITE  
  
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

14 启用 Apply service

SQL>  alter database recover managed standby database disconnect from session;

Database altered.

SQL>  select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

15 验证 DG 同步情况

--主库

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
	    70

SQL>  alter system switch logfile;

System altered.

SQL>  alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
	    72

--主库建一张表测试

SQL> create table test (x number(10));

Table created.

SQL> insert into test values(10);

1 row created.

SQL> insert into test values(10);

1 row created.

SQL> insert into test values(10);

1 row created.

SQL> commit;

Commit complete.

SQL>  alter system switch logfile;

System altered.

--备库

SQL>  select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
	    72

SQL>  select sequence#,applied from v$archived_log;

 SEQUENCE# APPLIED
---------- ---------
	68 YES
	70 YES
	69 YES
	71 YES
	72 YES

这里备库 applied 正常说明是同步的。

因为 Oracle 11g 的备库是 Read only 方式打开的，我们还可以在主库创建一张表，然后在备库来查询。

--备库测试表

SQL> desc test;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 X						    NUMBER(10)

SQL> select * from test;

	 X
----------
	10
	10
	10

16 查看 DG 的的模式：

SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

签到成功

CNDBA社区

ORACLE COPY数据文件方式搭建DG

leo

QQ交流群

注册联系QQ

签到成功

CNDBA社区

ORACLE COPY数据文件方式 搭建DG

leo

QQ交流群

注册联系QQ

ORACLE COPY数据文件方式搭建DG