sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 产品DBA > Oracle DG >

【学习笔记】Oracle 11GR2搭建活动的物理DG/DATAGRUAD详细步骤案例

时间:2016-10-25 10:52   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库DATAGRUAD的配置笔记,在VM环境中配置Oracle 11GR2 DG的笔记。
11GR2+DG,11GR2,VM里面搭建一个11GR2+DG的测试环境,STANDBY启动到READ ONLY

测试环境为:OS REDHAT 5.6 X86_64,DB 11.2.0.2

安装环境与创建数据库这里就不用说了,很简单的。

整个环境为成4个步骤:

1,主备修改

1.1 修改参数

1.2 修改监听

1.3 cp相关文件到standby上面

1.4 创建standby日志

2,备备修改

2.1 修改参数

2.2 修改监听与测试

2.3 启动到mount

2.4 创建相关目录

3,主备duplicate数据库

4,测试DG是否成功

正在开始正式的测试过程

1.1 查看数据库是否在归档与是否强制LOGGING模式。

SQL> select log_mode,force_logging from v$database;

LOG_MODE FOR
------------ ---
ARCHIVELOG NO

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

1.2 查看数据库的日志组个数与大小,因为我们创建standby日志组的个数是原日志组个数+1再与thread的积,size不能小于原日志文件的大小。

SQL> select group#,bytes/1024/1024 from v$log;

GROUP# BYTES/1024/1024
---------- ---------------
1 50
2 50
3 50

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/htz/redo03.log
/u01/app/oracle/oradata/htz/redo02.log
/u01/app/oracle/oradata/htz/redo01.log

1.3 创建standby日志组,位置与原日志组相同的路径。创建完成后查询是否成功

SQL> alter database add standby logfile '/u01/app/oracle/oradata/htz/standby01.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/htz/standby02.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/htz/standby03.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/htz/standby04.log' size 50m;

Database altered.
SQL> select group#,status,type,member from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/htz/redo03.log
2 ONLINE /u01/app/oracle/oradata/htz/redo02.log
1 ONLINE /u01/app/oracle/oradata/htz/redo01.log
4 STANDBY /u01/app/oracle/oradata/htz/standby01.log
5 STANDBY /u01/app/oracle/oradata/htz/standby02.log
6 STANDBY /u01/app/oracle/oradata/htz/standby03.log
7 STANDBY /u01/app/oracle/oradata/htz/standby04.log

1.4 修改相关的参数,与DG的参数就只与几个参数相关,大概就是日志,文件的位置的转换,GAP的处理,其实GAP已经会自动的处理,不过这里我们还是介绍配置FAL_SERVER,FAL_CLIENT参数。

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(htz,htzb)';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch/htz/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=htz';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=htzb LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=htzb';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;

System altered.

SQL> alter system set FAL_SERVER=htzb;

System altered.

SQL> alter system set FAL_CLIENT=htz;

System altered.

SQL> alter system set db_unique_name=htz scope=spfile;

System altered.

SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/htz/','/u01/app/oracle/oradata/htz/' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/htz/','/u01/app/oracle/oradata/htz/' scope=spfile;

System altered.

SQL> create pfile from spfile;

File created.

1.5 把dbs下的内容同步到standby主机上面,主要是密码文件(一定要复制过去的)与init文件。

[oracle@11g admin]$ rsync -avlR /u01/app/oracle/product/11.2.0/db_1/dbs/* 192.168.100.31:/
oracle@192.168.100.31's password:
building file list ... done
/u01/
/u01/app/
/u01/app/oracle/
/u01/app/oracle/product/
/u01/app/oracle/product/11.2.0/
/u01/app/oracle/product/11.2.0/db_1/
/u01/app/oracle/product/11.2.0/db_1/dbs/
/u01/app/oracle/product/11.2.0/db_1/dbs/hc_DBUA0.dat
/u01/app/oracle/product/11.2.0/db_1/dbs/hc_htz.dat
/u01/app/oracle/product/11.2.0/db_1/dbs/hc_luo.dat
/u01/app/oracle/product/11.2.0/db_1/dbs/init.ora
/u01/app/oracle/product/11.2.0/db_1/dbs/inithtz.ora
/u01/app/oracle/product/11.2.0/db_1/dbs/lkHTZ
/u01/app/oracle/product/11.2.0/db_1/dbs/lkHTZA
/u01/app/oracle/product/11.2.0/db_1/dbs/orapwhtz
/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_htz.f
/u01/app/oracle/product/11.2.0/db_1/dbs/spfilehtz.ora

sent 9764651 bytes received 282 bytes 161403.85 bytes/sec
total size is 9762574 speedup is 1.00

1.6 监听的修改,特别注意这里我们使用了静态的监听,是为了以后我们测试broker时使用的,如果你不用这个,那边可以用动态监听,

$ lsnrctl stop

LSNRCTL for Solaris: Version 11.2.0.1.0 - Production on 31-AUG-2012 21:38:51

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.30)(PORT=1521)))
The command completed successfully
[oracle@11g admin]$ cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = htz)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = htz)
)
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.30)(PORT = 1521))
)
[oracle@11g admin]$ cat tnsnames.ora
HTZB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.31)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = dedicate)
(SERVICE_NAME = htzb)
)
)

HTZ =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.30)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = dedicate)
(SERVICE_NAME = htz)
)
)

启动监听
[oracle@11g admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 07-SEP-2012 05:35:50

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.2.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/11g/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.30)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.30)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 07-SEP-2012 05:35:50
Uptime 0 days 0 hr. 0 min. 0 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/11g/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.30)(PORT=1521)))
Services Summary...
Service "htz" has 1 instance(s).
Instance "htz", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
主库上面修改的内容差不多就是这些了。

下面就是库备的修改了。

2.1 修改监听

[oracle@11gdg 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 = htzb)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = htz)
)
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.31)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle

[oracle@11gdg 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.


HTZB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.31)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = dedicate)
(SERVICE_NAME = htzb)
)
)

HTZ =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.30)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = dedicate)
(SERVICE_NAME = htz)
)
)
[oracle@11gdg admin]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 06-SEP-2012 18:13:17

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.31)(PORT=1521)))
The command completed successfully
[oracle@11gdg admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 06-SEP-2012 18:13:23

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.2.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/11gdg/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.31)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.31)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 06-SEP-2012 18:13:24
Uptime 0 days 0 hr. 0 min. 0 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/11gdg/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.31)(PORT=1521)))
Services Summary...
Service "htzb" has 1 instance(s).
Instance "htz", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

测试监听是否正常
[oracle@11gdg /]$ sqlplus sys/oracle@htz as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 6 18:19:20 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@11gdg /]$ sqlplus sys/oracle@htzb as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 6 18:19:24 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to an idle instance.

2.1 创建相关文件与修改参数文件

创建相关目录
[oracle@11gdg admin]$ mkdir -p /u01/app/oracle/arch/htz
[oracle@11gdg admin]$ mkdir -p /u01/app/oracle/flash_recovery_area/htz
[oracle@11gdg admin]$ mkdir -p /u01/app/oracle/oradata/htz
[oracle@11gdg admin]$ mkdir -p /u01/app/oracle/admin/htz/adump
修改参数文件
[oracle@11gdg /]$ cd $ORACLE_HOME/dbs
[oracle@11gdg dbs]$ rm spfilehtz.ora
[oracle@11gdg dbs]$ cat inithtz.ora
htz.__db_cache_size=67108864
htz.__java_pool_size=4194304
htz.__large_pool_size=4194304
htz.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
htz.__pga_aggregate_target=134217728
htz.__sga_target=180355072
htz.__shared_io_pool_size=0
htz.__shared_pool_size=96468992
htz.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/htz/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/htz/control01.ctl','/u01/app/oracle/oradata/htz/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/htz/','/u01/app/oracle/oradata/htz/'
*.db_name='htz'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4227858432
*.db_unique_name='HTZB'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=htzXDB)'
*.fal_client='HTZB'
*.fal_server='HTZ'
*.log_archive_config='DG_CONFIG=(htz,htzb)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/arch/htz/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=htzb'
*.log_archive_dest_2='SERVICE=htz LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=htz'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='/u01/app/oracle/oradata/htz/','/u01/app/oracle/oradata/htz/'
*.memory_target=314572800
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

1.3 创建spfile与启动数据库到mount

创建spfile,并启动数据库到NOMOUNT
[oracle@11gdg dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 6 18:24:02 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 313159680 bytes
Fixed Size 2226072 bytes
Variable Size 239077480 bytes
Database Buffers 67108864 bytes
Redo Buffers 4747264 bytes
SQL> create spfile from pfile;

File created.

SQL> startup force mount;
ORACLE instance started.

Total System Global Area 313159680 bytes
Fixed Size 2226072 bytes
Variable Size 239077480 bytes
Database Buffers 67108864 bytes
Redo Buffers 4747264 bytes
ORA-00205: error in identifying control file, check alert log for more info


SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/db_1/dbs/spfilehtz.ora

1.4 修改/etc/oratab文件,由于OS不一样,这里修改的位置也不一样,如果SUN /var/opt/oracle/oratab,IBM的/etc/oratab

[root@11gdg ~]# echo "htz:/u01/app/oracle/product/11.2.0/db_1:N">>/etc/oratab
[root@11gdg ~]# tail -1 /etc/oratab
htz:/u01/app/oracle/product/11.2.0/db_1:N
备库的相关操作到此就差不多,到了duplicate的时候了。

回到主备上面。

在前面我们修改了两个参数(db/log_file_name_convertt)到spfile文件中,还没有重启数据库,这里我们重启一下主数据库

3.1

[oracle@11g admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Sep 7 05:50:13 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

Total System Global Area 313159680 bytes
Fixed Size 2226072 bytes
Variable Size 247466088 bytes
Database Buffers 58720256 bytes
Redo Buffers 4747264 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

3.2 duplicate开始

[oracle@11g admin]$ rman target sys/oracle@htz auxiliary sys/oracle@htzb

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Sep 7 05:50:46 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: HTZ (DBID=1848107928)
connected to auxiliary database: HTZ (not mounted)

RMAN> run {
2> allocate channel prmy1 type disk;
3> allocate channel prmy2 type disk;
4> allocate channel prmy3 type disk;
5> allocate channel prmy4 type disk;
6> allocate auxiliary channel stby type disk;
7> duplicate target database for standby from active database nofilenamecheck
8> ;
9> }

using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=36 device type=DISK

allocated channel: prmy2
channel prmy2: SID=37 device type=DISK

allocated channel: prmy3
channel prmy3: SID=38 device type=DISK

allocated channel: prmy4
channel prmy4: SID=39 device type=DISK

allocated channel: stby
channel stby: SID=21 device type=DISK

Starting Duplicate Db at 07-SEP-12

contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwhtz' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapwhtz' ;
}
executing Memory Script

Starting backup at 07-SEP-12
Finished backup at 07-SEP-12

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/htz/control01.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/htz/control02.ctl' from
'/u01/app/oracle/oradata/htz/control01.ctl';
}
executing Memory Script

Starting backup at 07-SEP-12
channel prmy1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_htz.f tag=TAG20120907T055127 RECID=1 STAMP=793345887
channel prmy1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 07-SEP-12

Starting restore at 07-SEP-12

channel stby: copied control file copy
Finished restore at 07-SEP-12

contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/htz/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/htz/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/htz/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/htz/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/htz/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/htz/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/htz/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/htz/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/htz/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/htz/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 07-SEP-12
channel prmy1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/htz/system01.dbf
channel prmy2: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/htz/sysaux01.dbf
channel prmy3: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/htz/undotbs01.dbf
channel prmy4: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/htz/users01.dbf
output file name=/u01/app/oracle/oradata/htz/undotbs01.dbf tag=TAG20120907T055135
channel prmy3: datafile copy complete, elapsed time: 00:00:08
output file name=/u01/app/oracle/oradata/htz/users01.dbf tag=TAG20120907T055135
channel prmy4: datafile copy complete, elapsed time: 00:00:08
output file name=/u01/app/oracle/oradata/htz/sysaux01.dbf tag=TAG20120907T055135
channel prmy2: datafile copy complete, elapsed time: 00:00:56
output file name=/u01/app/oracle/oradata/htz/system01.dbf tag=TAG20120907T055135
channel prmy1: datafile copy complete, elapsed time: 00:01:06
Finished backup at 07-SEP-12

sql statement: alter system archive log current

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=793304890 file name=/u01/app/oracle/oradata/htz/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=793304890 file name=/u01/app/oracle/oradata/htz/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=793304890 file name=/u01/app/oracle/oradata/htz/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=793304890 file name=/u01/app/oracle/oradata/htz/users01.dbf
Finished Duplicate Db at 07-SEP-12
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: stby
RMAN> exit


Recovery Manager complete.
下面把备库启动到open only下面。并recover。

SQL> alter database open read only;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.


在alert日志中可以看到下面的内容:

Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /u01/app/oracle/arch/htz/1_6_793343515.dbf
Media Recovery Log /u01/app/oracle/arch/htz/1_7_793343515.dbf
Media Recovery Log /u01/app/oracle/arch/htz/1_8_793343515.dbf
Media Recovery Log /u01/app/oracle/arch/htz/1_9_793343515.dbf
Completed: ALTER DATABASE RECOVER managed standby database using current logfile disconnect
Media Recovery Waiting for thread 1 sequence 10 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 10 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/htz/standby01.log
下面就是开始测试了。这里我们是用的SCOTT这个用户来做测试,在SCOTT用户下面创建一个表,在备库去查询看是否创建成功,与在主库上面SWIRCH一次日志,看alert中的日志输出内容。

在主库上面做测试
[oracle@11g admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Sep 7 05:57:41 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database set standby to maximize availability;

Database altered.

SQL> conn scott/tiger;
ERROR:
ORA-28000: the account is locked


Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> alter user scott identified by oracle;

User altered.

SQL> alter user scott account unlock;

User altered.

SQL> create table scott.test1 as select * from dba_objects;

Table created.

备库上面

SQL> select count(*) from scott.test1;

COUNT(*)
----------
72391

说明已经同步

主库上面刷新日志。

SQL> conn / as sysdba
Connected.
SQL> alter system archive log current;

System altered.

日志
ALTER SYSTEM ARCHIVE LOG
Fri Sep 07 06:04:45 2012
LGWR: Standby redo logfile selected to archive thread 1 sequence 11
LGWR: Standby redo logfile selected for thread 1 sequence 11 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 11 (LGWR switch)
Current log# 2 seq# 11 mem# 0: /u01/app/oracle/oradata/htz/redo02.log
Fri Sep 07 06:04:48 2012
Archived Log entry 12 added for thread 1 sequence 10 ID 0x6e285e98 dest 1:

备库日志

Standby controlfile consistent with primary
RFS[5]: Selected log 5 for thread 1 sequence 11 dbid 1848107928 branch 793343515
Thu Sep 06 18:40:13 2012
由于换了一家公司,这家公司全部是用的11GR2+DG,11GR2没有玩过,DG也没有玩过,所以这里在自己的VM里面搭建一个11GR2+DG的测试环境,STANDBY启动到READ ONLY
测试环境为:OS REDHAT 5.6 X86_64,DB 11.2.0.2
安装环境与创建数据库这里就不用说了,很简单的。
整个环境为成4个步骤:
1,主备修改
1.1 修改参数
1.2 修改监听
1.3 cp相关文件到standby上面
1.4 创建standby日志
2,备备修改
2.1 修改参数
2.2 修改监听与测试
2.3 启动到mount
2.4 创建相关目录
3,主备duplicate数据库
4,测试DG是否成功
正在开始正式的测试过程

1.1 查看数据库是否在归档与是否强制LOGGING模式。
SQL> select log_mode,force_logging from v$database;

LOG_MODE FOR
------------ ---
ARCHIVELOG NO

SQL> ALTERDATABASEFORCE LOGGING;

Database altered.

1.2 查看数据库的日志组个数与大小,因为我们创建standby日志组的个数是原日志组个数+1再与thread的积,size不能小于原日志文件的大小。

SQL> selectgroup#,bytes/1024/1024 from v$log;

GROUP# BYTES/1024/1024
---------- ---------------
1 50
2 50
3 50

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/htz/redo03.log
/u01/app/oracle/oradata/htz/redo02.log
/u01/app/oracle/oradata/htz/redo01.log

1.3 创建standby日志组,位置与原日志组相同的路径。创建完成后查询是否成功

SQL> alterdatabaseadd standby logfile '/u01/app/oracle/oradata/htz/standby01.log'size 50m;

Database altered.

SQL> alterdatabaseadd standby logfile '/u01/app/oracle/oradata/htz/standby02.log'size 50m;

Database altered.

SQL> alterdatabaseadd standby logfile '/u01/app/oracle/oradata/htz/standby03.log'size 50m;

Database altered.

SQL> alterdatabaseadd standby logfile '/u01/app/oracle/oradata/htz/standby04.log'size 50m;

Database altered.
SQL> selectgroup#,status,type,member from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/htz/redo03.log
2 ONLINE /u01/app/oracle/oradata/htz/redo02.log
1 ONLINE /u01/app/oracle/oradata/htz/redo01.log
4 STANDBY /u01/app/oracle/oradata/htz/standby01.log
5 STANDBY /u01/app/oracle/oradata/htz/standby02.log
6 STANDBY /u01/app/oracle/oradata/htz/standby03.log
7 STANDBY /u01/app/oracle/oradata/htz/standby04.log

1.4 修改相关的参数,与DG的参数就只与几个参数相关,大概就是日志,文件的位置的转换,GAP的处理,其实GAP已经会自动的处理,不过这里我们还是介绍配置FAL_SERVER,FAL_CLIENT参数。

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(htz,htzb)';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch/htz/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=htz';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=htzb LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=htzb';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;

System altered.

SQL> alter system set FAL_SERVER=htzb;

System altered.

SQL> alter system set FAL_CLIENT=htz;

System altered.

SQL> alter system set db_unique_name=htz scope=spfile;

System altered.

SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/htz/','/u01/app/oracle/oradata/htz/' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/htz/','/u01/app/oracle/oradata/htz/' scope=spfile;

System altered.

SQL> create pfile from spfile;

File created.

1.5 把dbs下的内容同步到standby主机上面,主要是密码文件(一定要复制过去的)与init文件。

[oracle@11g admin]$ rsync -avlR /u01/app/oracle/product/11.2.0/db_1/dbs/* 192.168.100.31:/
oracle@192.168.100.31's password:
building file list ... done
/u01/
/u01/app/
/u01/app/oracle/
/u01/app/oracle/product/
/u01/app/oracle/product/11.2.0/
/u01/app/oracle/product/11.2.0/db_1/
/u01/app/oracle/product/11.2.0/db_1/dbs/
/u01/app/oracle/product/11.2.0/db_1/dbs/hc_DBUA0.dat
/u01/app/oracle/product/11.2.0/db_1/dbs/hc_htz.dat
/u01/app/oracle/product/11.2.0/db_1/dbs/hc_luo.dat
/u01/app/oracle/product/11.2.0/db_1/dbs/init.ora
/u01/app/oracle/product/11.2.0/db_1/dbs/inithtz.ora
/u01/app/oracle/product/11.2.0/db_1/dbs/lkHTZ
/u01/app/oracle/product/11.2.0/db_1/dbs/lkHTZA
/u01/app/oracle/product/11.2.0/db_1/dbs/orapwhtz
/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_htz.f
/u01/app/oracle/product/11.2.0/db_1/dbs/spfilehtz.ora

sent 9764651 bytes received 282 bytes 161403.85 bytes/sec
total sizeis 9762574 speedup is 1.00

1.6 监听的修改,特别注意这里我们使用了静态的监听,是为了以后我们测试broker时使用的,如果你不用这个,那边可以用动态监听,

$ lsnrctl stop

LSNRCTL for Solaris: Version 11.2.0.1.0 - Production on 31-AUG-2012 21:38:51

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.30)(PORT=1521)))
The command completed successfully
[oracle@11g admin]$ cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = htz)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = htz)
)
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.30)(PORT = 1521))
)
[oracle@11g admin]$ cat tnsnames.ora
HTZB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.31)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = dedicate)
(SERVICE_NAME = htzb)
)
)

HTZ =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.30)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = dedicate)
(SERVICE_NAME = htz)
)
)

启动监听
[oracle@11g admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 07-SEP-2012 05:35:50

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.2.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/11g/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.30)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.30)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 07-SEP-2012 05:35:50
Uptime 0 days 0 hr. 0 min. 0 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/11g/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.30)(PORT=1521)))
Services Summary...
Service "htz" has 1 instance(s).
Instance "htz", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

主库上面修改的内容差不多就是这些了。

下面就是库备的修改了。

2.1 修改监听

[oracle@11gdg 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 = htzb)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = htz)
)
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.31)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle

[oracle@11gdg 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.


HTZB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.31)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = dedicate)
(SERVICE_NAME = htzb)
)
)

HTZ =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.30)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = dedicate)
(SERVICE_NAME = htz)
)
)
[oracle@11gdg admin]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 06-SEP-2012 18:13:17

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.31)(PORT=1521)))
The command completed successfully
[oracle@11gdg admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 06-SEP-2012 18:13:23

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.2.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/11gdg/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.31)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.31)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 06-SEP-2012 18:13:24
Uptime 0 days 0 hr. 0 min. 0 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/11gdg/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.31)(PORT=1521)))
Services Summary...
Service "htzb" has 1 instance(s).
Instance "htz", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

测试监听是否正常
[oracle@11gdg /]$ sqlplus sys/oracle@htz as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 6 18:19:20 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining andReal Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining andReal Application Testing options
[oracle@11gdg /]$ sqlplus sys/oracle@htzb as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 6 18:19:24 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to an idle instance.

2.1 创建相关文件与修改参数文件

创建相关目录
[oracle@11gdg admin]$ mkdir -p /u01/app/oracle/arch/htz
[oracle@11gdg admin]$ mkdir -p /u01/app/oracle/flash_recovery_area/htz
[oracle@11gdg admin]$ mkdir -p /u01/app/oracle/oradata/htz
[oracle@11gdg admin]$ mkdir -p /u01/app/oracle/admin/htz/adump
修改参数文件
[oracle@11gdg /]$ cd $ORACLE_HOME/dbs
[oracle@11gdg dbs]$ rm spfilehtz.ora
[oracle@11gdg dbs]$ cat inithtz.ora
htz.__db_cache_size=67108864
htz.__java_pool_size=4194304
htz.__large_pool_size=4194304
htz.__oracle_base='/u01/app/oracle'#ORACLE_BASE setfrom environment
htz.__pga_aggregate_target=134217728
htz.__sga_target=180355072
htz.__shared_io_pool_size=0
htz.__shared_pool_size=96468992
htz.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/htz/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/htz/control01.ctl','/u01/app/oracle/oradata/htz/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/htz/','/u01/app/oracle/oradata/htz/'
*.db_name='htz'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4227858432
*.db_unique_name='HTZB'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=htzXDB)'
*.fal_client='HTZB'
*.fal_server='HTZ'
*.log_archive_config='DG_CONFIG=(htz,htzb)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/arch/htz/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=htzb'
*.log_archive_dest_2='SERVICE=htz LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=htz'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='/u01/app/oracle/oradata/htz/','/u01/app/oracle/oradata/htz/'
*.memory_target=314572800
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

1.3 创建spfile与启动数据库到mount

创建spfile,并启动数据库到NOMOUNT
[oracle@11gdg dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 6 18:24:02 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 313159680 bytes
Fixed Size 2226072 bytes
Variable Size 239077480 bytes
Database Buffers 67108864 bytes
Redo Buffers 4747264 bytes
SQL> create spfile from pfile;

File created.

SQL> startup force mount;
ORACLE instance started.

Total System Global Area 313159680 bytes
Fixed Size 2226072 bytes
Variable Size 239077480 bytes
Database Buffers 67108864 bytes
Redo Buffers 4747264 bytes
ORA-00205: error in identifying control file, check alert log for more info


SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/db_1/dbs/spfilehtz.ora

1.4 修改/etc/oratab文件,由于OS不一样,这里修改的位置也不一样,如果SUN /var/opt/oracle/oratab,IBM的/etc/oratab

[root@11gdg ~]# echo "htz:/u01/app/oracle/product/11.2.0/db_1:N">>/etc/oratab
[root@11gdg ~]# tail -1 /etc/oratab
htz:/u01/app/oracle/product/11.2.0/db_1:N

备库的相关操作到此就差不多,到了duplicate的时候了。

回到主备上面。

在前面我们修改了两个参数(db/log_file_name_convertt)到spfile文件中,还没有重启数据库,这里我们重启一下主数据库

3.1

[oracle@11g admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Sep 7 05:50:13 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining andReal Application Testing options

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

Total System Global Area 313159680 bytes
Fixed Size 2226072 bytes
Variable Size 247466088 bytes
Database Buffers 58720256 bytes
Redo Buffers 4747264 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining andReal Application Testing options

3.2 duplicate开始

[oracle@11g admin]$ rman target sys/oracle@htz auxiliary sys/oracle@htzb

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Sep 7 05:50:46 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: HTZ (DBID=1848107928)
connected to auxiliary database: HTZ (not mounted)

RMAN> run {
2> allocate channel prmy1 type disk;
3> allocate channel prmy2 type disk;
4> allocate channel prmy3 type disk;
5> allocate channel prmy4 type disk;
6> allocate auxiliary channel stby type disk;
7> duplicate target databasefor standby from active database nofilenamecheck
8> ;
9> }

using target database control file insteadof recovery catalog
allocated channel: prmy1
channel prmy1: SID=36 device type=DISK

allocated channel: prmy2
channel prmy2: SID=37 device type=DISK

allocated channel: prmy3
channel prmy3: SID=38 device type=DISK

allocated channel: prmy4
channel prmy4: SID=39 device type=DISK

allocated channel: stby
channel stby: SID=21 device type=DISK

Starting Duplicate Db at 07-SEP-12

contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwhtz' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapwhtz' ;
}
executing Memory Script

Starting backup at 07-SEP-12
Finished backup at 07-SEP-12

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/htz/control01.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/htz/control02.ctl'from
'/u01/app/oracle/oradata/htz/control01.ctl';
}
executing Memory Script

Starting backup at 07-SEP-12
channel prmy1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_htz.f tag=TAG20120907T055127 RECID=1 STAMP=793345887
channel prmy1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 07-SEP-12

Starting restore at 07-SEP-12

channel stby: copied control file copy
Finished restore at 07-SEP-12

contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alterdatabase mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/htz/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/htz/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/htz/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/htz/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/htz/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/htz/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/htz/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/htz/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/htz/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/htz/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 07-SEP-12
channel prmy1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/htz/system01.dbf
channel prmy2: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/htz/sysaux01.dbf
channel prmy3: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/htz/undotbs01.dbf
channel prmy4: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/htz/users01.dbf
output file name=/u01/app/oracle/oradata/htz/undotbs01.dbf tag=TAG20120907T055135
channel prmy3: datafile copy complete, elapsed time: 00:00:08
output file name=/u01/app/oracle/oradata/htz/users01.dbf tag=TAG20120907T055135
channel prmy4: datafile copy complete, elapsed time: 00:00:08
output file name=/u01/app/oracle/oradata/htz/sysaux01.dbf tag=TAG20120907T055135
channel prmy2: datafile copy complete, elapsed time: 00:00:56
output file name=/u01/app/oracle/oradata/htz/system01.dbf tag=TAG20120907T055135
channel prmy1: datafile copy complete, elapsed time: 00:01:06
Finished backup at 07-SEP-12

sql statement: alter system archive log current

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=793304890 file name=/u01/app/oracle/oradata/htz/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=793304890 file name=/u01/app/oracle/oradata/htz/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=793304890 file name=/u01/app/oracle/oradata/htz/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=793304890 file name=/u01/app/oracle/oradata/htz/users01.dbf
Finished Duplicate Db at 07-SEP-12
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: stby
RMAN> exit


Recovery Manager complete.

下面把备库启动到open only下面。并recover。

SQL> alterdatabaseopenreadonly;

Database altered.

SQL> alterdatabase recover managed standby database using current logfile disconnect from session;

Database altered.


在alert日志中可以看到下面的内容:

Waiting forall non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /u01/app/oracle/arch/htz/1_6_793343515.dbf
Media Recovery Log /u01/app/oracle/arch/htz/1_7_793343515.dbf
Media Recovery Log /u01/app/oracle/arch/htz/1_8_793343515.dbf
Media Recovery Log /u01/app/oracle/arch/htz/1_9_793343515.dbf
Completed: ALTERDATABASE RECOVER managed standby database using current logfile disconnect
Media Recovery Waiting for thread 1 sequence 10 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 10 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/htz/standby01.log

下面就是开始测试了。这里我们是用的SCOTT这个用户来做测试,在SCOTT用户下面创建一个表,在备库去查询看是否创建成功,与在主库上面SWIRCH一次日志,看alert中的日志输出内容。

在主库上面做测试
[oracle@11g admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Sep 7 05:57:41 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining andReal Application Testing options

SQL> alterdatabaseset standby to maximize availability;

Database altered.

SQL> conn scott/tiger;
ERROR:
ORA-28000: the account is locked


Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> alteruser scott identified by oracle;

User altered.

SQL> alteruser scott account unlock;

User altered.

SQL> createtable scott.test1 asselect * from dba_objects;

Table created.

备库上面

SQL> selectcount(*) from scott.test1;

COUNT(*)
----------
72391

说明已经同步

主库上面刷新日志。

SQL> conn / as sysdba
Connected.
SQL> alter system archive log current;

System altered.

日志
ALTER SYSTEM ARCHIVE LOG
Fri Sep 07 06:04:45 2012
LGWR: Standby redo logfile selected to archive thread 1 sequence 11
LGWR: Standby redo logfile selected for thread 1 sequence 11 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 11 (LGWR switch)
Current log# 2 seq# 11 mem# 0: /u01/app/oracle/oradata/htz/redo02.log
Fri Sep 07 06:04:48 2012
Archived Log entry 12 added for thread 1 sequence 10 ID 0x6e285e98 dest 1:

备库日志

Standby controlfile consistent withprimary
RFS[5]: Selected log 5 for thread 1 sequence 11 dbid 1848107928 branch 793343515
Thu Sep 06 18:40:13 2012
Media Recovery Waiting for thread 1 sequence 11 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 11 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/htz/standby02.log
Thu Sep 06 18:40:16 2012
Archived Log entry 5 added for thread 1 sequence 10 ID 0x6e285e98 dest 1:



System altered.

Media Recovery Waiting for thread 1 sequence 11 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 11 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/htz/standby02.log
Thu Sep 06 18:40:16 2012
Archived Log entry 5 added for thread 1 sequence 10 ID 0x6e285e98 dest 1


System altered.


本文固定链接: http://www.htz.pw/2014/06/18/11gr2-datagruad-%e7%8e%af%e5%a2%83%e6%90%ad%e5%bb%baborker.html | 认真就输


       

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle 11GR2搭建活动的物理DG/DATAGRUAD详细步骤案例

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1063.html

Oracle研究中心

关键词:

在VM环境中配置Oracle DG笔记

Oracle 11GR2配置DG的详细过程

Oracle DATAGRUAD的配置笔记

Oracle DG的官方配置方法