今天，有同事问ORACLE 11.2.0.4 dataguard是否对DDL支持；由于，我工作中oracle 11g的dataguard环境很少，所以对她的研究不是很多；因此，对于有疑问的知识，最好的办法就是用实验数据进行验证了。
首先，先说明下实验结论：ORACLE 11.2.0.4的dataguard对DDL是支持的。
服务器环境：
主库
[oracle@oradbs ~]$ uname -a
Linux oradbs 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
[oracle@oradbs ~]$
备库
[oracle@oratest ~]$ uname -a
Linux oratest 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
[oracle@oratest ~]$
数据库版本：
主库
[oracle@oradbs ~]$ sqlplus -v
SQL*Plus: Release 11.2.0.4.0 Production
[oracle@oradbs ~]$
备库
[oracle@oratest ~]$ sqlplus -v
SQL*Plus: Release 11.2.0.4.0 Production
[oracle@oratest ~]$
搭建好的11.2.0.4 oracle dataguard:
主库：
[oracle@oradbs ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 1 09:07:11 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
select name,database_role from v$database;
NAME DATABASE_ROLE
--------- ----------------
ORADB PRIMARY
SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID =2;
STATUS GAP_STATUS
--------- ------------------------
VALID RESOLVABLE GAP
备库：
[oracle@oratest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 1 08:17:36 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name,database_role from v$database;
NAME DATABASE_ROLE
--------- ----------------
ORADB PHYSICAL STANDBY
SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID =2;
STATUS GAP_STATUS
--------- ------------------------
VALID NO GAP
SQL> COL NAME FOR A30
SQL> COL VALUE FOR A40
SQL> SET LINESIZE 1000
SQL> SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS
NAME VALUE DATUM_TIME
------------------------------ ---------------------------------------- ------------------------------
transport lag +00 00:00:00 08/31/2016 22:52:56
apply lag +00 00:00:00 08/31/2016 22:52:56
apply finish time
estimated startup time 5
SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
19 31-AUG-16 31-AUG-16 YES
oracle 11.2.0.4 dataguard对DDL支持测试：
验证用户创建语句：
主库：
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradata/arch
Oldest online log sequence 21
Next log sequence to archive 23
Current log sequence 23
SQL> create user zhul identified by zhul;
User created.
SQL> alter system switch logfile;
System altered.
备库：
SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
19 31-AUG-16 31-AUG-16 YES
20 31-AUG-16 31-AUG-16 YES
21 31-AUG-16 31-AUG-16 YES
22 31-AUG-16 31-AUG-16 YES
SQL> select username from dba_users where username='ZHUL';
no rows selected
SQL> /
USERNAME
------------------------------
ZHUL
注意：主库创建完用户，备库立即查询是查不到的，原因是当前的DATAGUARD是最大性能模式（如下图），需要主库切换归档并且备库应用完归档后，主库的用户创建才能查询到。
验证表空间创建语句：
主库：
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/oracle/oradata/oradb/users01.dbf
/home/oracle/oracle/oradata/oradb/undotbs01.dbf
/home/oracle/oracle/oradata/oradb/sysaux01.dbf
/home/oracle/oracle/oradata/oradb/system01.dbf
SQL> create tablespace test datafile '/home/oracle/oracle/oradata/oradb/test.dbf' size 100m;
Tablespace created.
SQL> alter system switch logfile;
System altered.
备库：
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/oracle/oradata/oradb/users01.dbf
/home/oracle/oracle/oradata/oradb/undotbs01.dbf
/home/oracle/oracle/oradata/oradb/sysaux01.dbf
/home/oracle/oracle/oradata/oradb/system01.dbf
SQL> /
FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/oracle/oradata/oradb/users01.dbf
/home/oracle/oracle/oradata/oradb/undotbs01.dbf
/home/oracle/oracle/oradata/oradb/sysaux01.dbf
/home/oracle/oracle/oradata/oradb/system01.dbf
/home/oracle/oracle/oradata/oradb/test.dbf
ORACLE 11.2.0.4 dg搭建及对DDL的支持验证最终结论是：ORACLE 11.2.0.4 对DDL是支持的。
本次测试环境的搭建过程：
主备节点主机：RHEL5.5
数据库版本：ORACLE 11.2.0.4
备库创建方法：可以使用RMAN备份主库到备库主机进行恢复，也可以使用duplicate直接在主库主机复制主库到备机生成备库，本次实验采用duplicate复制主库到备机生成备库的方法
前提：在相同平台操作系统下安装相同版本oracle11.2.0.4的数据库软件，不创建数据库
声明：本次DATAGURAD的搭建，仅仅是对DDL支持的验证实验，数据库部署环境及参数设置均不是合理的
调整主备机的监听及TNS解析文件
1、主库
[oracle@oradbs admin]$ cat listener.ora
# listener.ora Network Configuration File: /home/oracle/oracle/product/11.2.0.3/db/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oradbs)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_listener=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=oradb)
(SID_NAME=oradb)
(ORACLE_HOME=/home/oracle/oracle/product/11.2.0.3/db)))
ADR_BASE_LISTENER = /home/oracle/oracle
[oracle@oradbs admin]$
[oracle@oradbs admin]$ cat tnsnames.ora
beiku =(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.3)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oradb)
)
)
zhuku = (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oradb)
)
)
[oracle@oradbs admin]$
[oracle@oradbs ~]$ cat .bash_profile
# .bash_profile
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
PATH=$PATH:$HOME/bin
export PATH
export ORACLE_BASE=/home/oracle/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.3/db
export ORACLE_SID=oradb
export NLS_LANG=
export PATH=$PATH:$ORACLE_HOME/bin
[oracle@oradbs ~]$
2、备库
[oracle@oratest admin]$ cat listener.ora
# listener.ora Network Configuration File: /oradata/oracle/product/11.2.0.4/db/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oratest)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_listener=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=oradb)
(SID_NAME=oradb)
(ORACLE_HOME=/oradata/oracle/product/11.2.0.4/db)))
ADR_BASE_LISTENER = /oradata/oracle
[oracle@oratest admin]$
[oracle@oratest admin]$ cat tnsnames.ora
beiku =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.3)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oradb)
)
)
zhuku =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oradb)
)
)
[oracle@oratest admin]$
[oracle@oratest ~]$ cat .bash_profile
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
PATH=$PATH:$HOME/bin
export PATH
export ORACLE_BASE=/oradata/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db
export ORACLE_SID=oradb
export ORACLE_UNQNAME=beiku
export NLS_LANG=
export PATH=$PATH:$ORACLE_HOME/bin
主库的主要操作
1、主库设置归档模式，启动强日志模式
SQL> select log_mode,force_logging from v$database;
LOG_MODE FOR
------------ ---
ARCHIVELOG NO
SQL> alter database force logging;
Database altered.
SQL> select log_mode,force_logging from v$database;
LOG_MODE FOR
------------ ---
ARCHIVELOG YES
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/oracle/product/11.2.0.3/db/dbs/arch
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
2、主库修改归档路径
SQL> alter system set log_archive_dest_1='LOCATION=/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradb';
System altered.
SQL> alter system set log_archive_dest_2='SERVICE=beiku async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradb';
System altered.
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=beiku async valid_for=
(online_logfile,primary_role)
db_unique_name=oradb
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string LOCATION=/oradata/arch VALID_F
OR=(ALL_LOGFILES,ALL_ROLES) DB
_UNIQUE_NAME=oradb
3、主库添加dg相关日志组
SQL>select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
3 ONLINE /home/oracle/oracle/oradata/oradb/redo03.log
2 ONLINE /home/oracle/oracle/oradata/oradb/redo02.log
1 ONLINE /home/oracle/oracle/oradata/oradb/redo01.log
3 rows selected.
SQL> alter database add standby logfile thread 1 group 4 ('/home/oracle/oracle/oradata/oradb/redo04.log') size 50M;
alter database add standby logfile thread 1 group 5 ('/home/oracle/oracle/oradata/oradb/redo05.log') size 50M;
alter database add standby logfile thread 1 group 6 ('/home/oracle/oracle/oradata/oradb/redo06.log') size 50M;
alter database add standby logfile thread 1 group 7 ('/home/oracle/oracle/oradata/oradb/redo07.log') size 50M;
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL> select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
3 ONLINE /home/oracle/oracle/oradata/oradb/redo03.log
2 ONLINE /home/oracle/oracle/oradata/oradb/redo02.log
1 ONLINE /home/oracle/oracle/oradata/oradb/redo01.log
4 STANDBY /home/oracle/oracle/oradata/oradb/redo04.log
5 STANDBY /home/oracle/oracle/oradata/oradb/redo05.log
6 STANDBY /home/oracle/oracle/oradata/oradb/redo06.log
7 STANDBY /home/oracle/oracle/oradata/oradb/redo07.log
7 rows selected.
4、主库修改归档最大进程数
SQL> show parameter log_archive_max
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes integer 4
SQL> alter system set log_archive_max_processes=30;
System altered.
SQL> show parameter log_archive_max
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes integer 30
5、主库修改dg日志控制参数
SQL> alter system set log_archive_config='dg_config=(oradb,beiku)';
System altered.
SQL> alter system set fal_server=beiku;
System altered.
SQL> alter system set fal_client=oradb;
System altered.
SQL> alter system set standby_file_management=auto;
System altered.
SQL> show parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string dg_config=(oradb,beiku)
6、主库生成启动备库的pfile参数文件
SQL> create pfile from spfile;
File created.
7、主库生成数据库的密码文件
[oracle@oradbs dbs]$ orapwd file=orapworadb password=oracle
8、传送pfile和密码文件到备机
[oracle@oradbs dbs]$ scp orapworadb 192.168.56.3:/oradata/oracle/product/11.2.0.4/db/dbs/
oracle@192.168.56.3's password:
orapworadb 100% 1536 1.5KB/s 00:00
[oracle@oradbs dbs]$ cd ..
[oracle@oradbs ~]$ scp pfile.ora 192.168.56.3:/home/oracle/
oracle@192.168.56.3's password:
pfile.ora 100% 1182 1.2KB/s 00:00
[oracle@oradbs ~]$
9、确认主库监听启动
[oracle@oradbs ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 01-SEP-2016 11:03:44
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oradbs)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 31-AUG-2016 23:01:26
Uptime 0 days 12 hr. 2 min. 18 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/oracle/product/11.2.0.3/db/network/admin/listener.ora
Listener Log File /home/oracle/oracle/diag/tnslsnr/oradbs/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oradbs)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "oradb" has 2 instance(s).
Instance "oradb", status UNKNOWN, has 1 handler(s) for this service...
Instance "oradb", status READY, has 1 handler(s) for this service...
Service "oradbXDB" has 1 instance(s).
Instance "oradb", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oradbs ~]$
备库的主要操作
1、修改/home/oracle/pfile.ora文件，
DB_UNIQUE_NAME=oradb
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=BEIKU
FAL_CLIENT=ORADB
STANDBY_FILE_MANAGEMENT=AUTO
2、备库使用pfile启动到nomount
[oracle@oratest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 31 22:32:46 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/home/oracle/pfile.ora';
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 683674664 bytes
Database Buffers 150994944 bytes
Redo Buffers 2355200 bytes
3、备库监听启动
[oracle@oratest admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 31-AUG-2016 22:11:38
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /oradata/oracle/product/11.2.0.4/db/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /oradata/oracle/product/11.2.0.4/db/network/admin/listener.ora
Log messages written to /oradata/oracle/diag/tnslsnr/oratest/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oratest)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oratest)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 31-AUG-2016 22:11:38
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oradata/oracle/product/11.2.0.4/db/network/admin/listener.ora
Listener Log File /oradata/oracle/diag/tnslsnr/oratest/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oratest)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "oradb" has 1 instance(s).
Instance "oradb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
4、监听及TNS解析服务可用性测试
[oracle@oratest admin]$ sqlplus sys/oracle@zhuku as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 31 22:12:39 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oratest admin]$
[oracle@oratest ~]$ sqlplus sys/oracle@beiku as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 1 10:36:28 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oratest admin]$
主库duplicate备库到备机
1、主库使用rman连接到备库实例
[oracle@oradbs admin]$ rman target sys/oracle@zhuku auxiliary sys/oracle@beiku
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Aug 31 23:26:57 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADB (DBID=2674606220)
connected to auxiliary database: ORADB (not mounted)
RMAN>
2、主库执行duplicate
RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;
Starting Duplicate Db at 31-AUG-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/home/oracle/oracle/product/11.2.0.3/db/dbs/orapworadb' auxiliary format
'/oradata/oracle/product/11.2.0.4/db/dbs/orapworadb' ;
}
executing Memory Script
Starting backup at 31-AUG-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=71 device type=DISK
Finished backup at 31-AUG-16
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/home/oracle/oracle/oradata/oradb/control01.ctl';
restore clone controlfile to '/home/oracle/oracle/oradata/oradb/control02.ctl' from
'/home/oracle/oracle/oradata/oradb/control01.ctl';
}
executing Memory Script
Starting backup at 31-AUG-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/home/oracle/oracle/product/11.2.0.3/db/dbs/snapcf_oradb.f tag=TAG20160831T232714 RECID=3 STAMP=921367635
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:02
Finished backup at 31-AUG-16
Starting restore at 31-AUG-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 31-AUG-16
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
"/home/oracle/oracle/oradata/oradb/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/home/oracle/oracle/oradata/oradb/system01.dbf";
set newname for datafile 2 to
"/home/oracle/oracle/oradata/oradb/sysaux01.dbf";
set newname for datafile 3 to
"/home/oracle/oracle/oradata/oradb/undotbs01.dbf";
set newname for datafile 4 to
"/home/oracle/oracle/oradata/oradb/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/home/oracle/oracle/oradata/oradb/system01.dbf" datafile
2 auxiliary format
"/home/oracle/oracle/oradata/oradb/sysaux01.dbf" datafile
3 auxiliary format
"/home/oracle/oracle/oradata/oradb/undotbs01.dbf" datafile
4 auxiliary format
"/home/oracle/oracle/oradata/oradb/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /home/oracle/oracle/oradata/oradb/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 31-AUG-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/home/oracle/oracle/oradata/oradb/system01.dbf
output file name=/home/oracle/oracle/oradata/oradb/system01.dbf tag=TAG20160831T232721
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/home/oracle/oracle/oradata/oradb/sysaux01.dbf
output file name=/home/oracle/oracle/oradata/oradb/sysaux01.dbf tag=TAG20160831T232721
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/home/oracle/oracle/oradata/oradb/undotbs01.dbf
output file name=/home/oracle/oracle/oradata/oradb/undotbs01.dbf tag=TAG20160831T232721
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/home/oracle/oracle/oradata/oradb/users01.dbf
output file name=/home/oracle/oracle/oradata/oradb/users01.dbf tag=TAG20160831T232721
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 31-AUG-16
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like "/oradata/arch/1_19_921341455.dbf" auxiliary format
"/oradata/arch/1_19_921341455.dbf" ;
catalog clone archivelog "/oradata/arch/1_19_921341455.dbf";
switch clone datafile all;
}
executing Memory Script
Starting backup at 31-AUG-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=19 RECID=16 STAMP=921367665
output file name=/oradata/arch/1_19_921341455.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 31-AUG-16
cataloged archived log
archived log file name=/oradata/arch/1_19_921341455.dbf RECID=1 STAMP=921364551
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=921364551 file name=/home/oracle/oracle/oradata/oradb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=921364551 file name=/home/oracle/oracle/oradata/oradb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=921364551 file name=/home/oracle/oracle/oradata/oradb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=921364551 file name=/home/oracle/oracle/oradata/oradb/users01.dbf
contents of Memory Script:
{
set until scn 984934;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 31-AUG-16
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 19 is already on disk as file /oradata/arch/1_19_921341455.dbf
archived log file name=/oradata/arch/1_19_921341455.dbf thread=1 sequence=19
media recovery complete, elapsed time: 00:00:00
Finished recover at 31-AUG-16
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/home/oracle/oracle/oradata/oradb/redo01.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 2 thread 1: '/home/oracle/oracle/oradata/oradb/redo02.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 3 thread 1: '/home/oracle/oracle/oradata/oradb/redo03.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 4 thread 1: '/home/oracle/oracle/oradata/oradb/redo04.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 5 thread 1: '/home/oracle/oracle/oradata/oradb/redo05.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 6 thread 1: '/home/oracle/oracle/oradata/oradb/redo06.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 7 thread 1: '/home/oracle/oracle/oradata/oradb/redo07.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
Finished Duplicate Db at 31-AUG-16
RMAN> quit
Recovery Manager complete.
备库创建dg相关的日志
SQL> alter database add standby logfile thread 1 group 12 ('/home/oracle/oracle/oradata/oradb/redo12.log') size 50M;
alter database add standby logfile thread 1 group 11 ('/home/oracle/oracle/oradata/oradb/redo11.log') size 50M;
alter database add standby logfile thread 1 group 10 ('/home/oracle/oracle/oradata/oradb/redo10.log') size 50M;
alter database add standby logfile thread 1 group 9 ('/home/oracle/oracle/oradata/oradb/redo09.log') size 50M;
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL>
Database altered.
备库将数据库置为只读模式
SQL> alter database open read only;
Database altered.
备库开启同步
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
查看是否有gap日志中断
SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
19 31-AUG-16 31-AUG-16 YES
主库切换日志
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradata/arch
Oldest online log sequence 18
Next log sequence to archive 20
Current log sequence 20
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradata/arch
Oldest online log sequence 20
Next log sequence to archive 22
Current log sequence 22
备库查看日志应用同步
SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
19 31-AUG-16 31-AUG-16 YES
20 31-AUG-16 31-AUG-16 YES
21 31-AUG-16 31-AUG-16 YES
查看主库告警日志，发现如下内容也说明dg搭建成功
Wed Aug 31 23:44:49 2016
ARC2: Archival destination is a Primary RAC instance: 'beiku'
Wed Aug 31 23:44:49 2016
ARCf: Archival destination is a Primary RAC instance: 'beiku'
Wed Aug 31 23:44:52 2016
Thread 1 advanced to log sequence 22 (LGWR switch)
Current log# 1 seq# 22 mem# 0: /home/oracle/oracle/oradata/oradb/redo01.log
Wed Aug 31 23:44:52 2016
Archived Log entry 18 added for thread 1 sequence 21 ID 0x9f6aea8c dest 1:
Wed Aug 31 23:44:52 2016
ARC4: Archival destination is a Primary RAC instance: 'beiku'
Wed Aug 31 23:44:52 2016
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LNS: Archival destination is a Primary RAC instance: 'beiku'
Wed Aug 31 23:48:38 2016
Thread 1 advanced to log sequence 23 (LGWR switch)
备库查看告警日志有如下内容：
Media Recovery Waiting for thread 1 sequence 20
Completed: alter database recover managed standby database using current logfile disconnect from session
Wed Aug 31 22:52:54 2016
RFS[1]: Assigned to RFS process 11189
RFS[1]: Opened log for thread 1 sequence 20 dbid -1620361076 branch 921341455
Archived Log entry 2 added for thread 1 sequence 20 rlc 921341455 ID 0x9f6aea8c dest 2:
Wed Aug 31 22:52:54 2016
Media Recovery Log /oradata/arch/1_20_921341455.dbf
Media Recovery Waiting for thread 1 sequence 21
Wed Aug 31 22:52:57 2016
RFS[2]: Assigned to RFS process 11193
RFS[2]: Opened log for thread 1 sequence 21 dbid -1620361076 branch 921341455
Archived Log entry 3 added for thread 1 sequence 21 rlc 921341455 ID 0x9f6aea8c dest 2:
如果实验中有不对的地方，敬请看客指正！
至此，ORACLE 11.2.0.4 DATAGUARD的实验环境搭建完成！