ITPub博客

首页 > 数据库 > Oracle > ORACLE 11.2.0.4 dg搭建及对DDL的支持验证

ORACLE 11.2.0.4 dg搭建及对DDL的支持验证

原创 Oracle 作者:清风艾艾 时间:2016-09-01 12:07:22 0 删除 编辑
    今天,有同事问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的实验环境搭建完成!







    

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29357786/viewspace-2124329/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
个人喜欢IT行业,目前从事数据库工作,包括Oracle、mysql、mongodb、sqlserver等数据库的维护,喜欢专研开发技术,尤其对java程序的开发感兴趣。工作经历上,在中国联通系统集成公司、中公网医疗信息技术有限公司做过数据库技术支持;目前在海量数据,负责华东区oracle、mysql、mongodb的维护工作。

注册时间:2015-01-30

  • 博文量
    179
  • 访问量
    214847