AI 助理
备案控制台
开发者社区 数据库 文章 正文

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

2016-09-01 1008
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据仓库AnalyticDB MySQL版，基础版 8ACU 100GB 1个月
日志服务 SLS，月写入数据量 50GB 1个月
简介：     今天，有同事问ORACLE 11.2.0.4 dataguard是否对DDL支持；由于，我工作中oracle 11g的dataguard环境很少，所以对她的研究不是很多；因此，对于有疑问的知识，最好的办法就是用实验数据进行验证了。
    今天，有同事问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的实验环境搭建完成！







    
文章标签：
云原生数据仓库AnalyticDB MySQL版
数据库备份
日志服务
关系型数据库
Oracle
SQL
数据库
网络协议
测试技术
OLAP
Linux
安全
关键词：
Oracle数据库一键上云dg
Oracle数据库一键上云验证
Oracle数据库一键上云ddl
Oracle数据库一键上云支持
相关实践学习
AnalyticDB MySQL海量数据秒级分析体验
快速上手AnalyticDB MySQL，玩转SQL开发等功能！本教程介绍如何在AnalyticDB MySQL中，一键加载内置数据集，并基于自动生成的查询脚本，运行复杂查询语句，秒级生成查询结果。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库，高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准，可以对海量数据进行即时的多维分析透视和业务探索，快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
kissweety
目录
相关文章
lemotree
oracle-故障-在dg备库使用DB LINK跨库查询时遇到ORA-16000
lemotree
1831 0
以山向海
|
4月前
|
Oracle 安全 关系型数据库
关系型数据库Oracle执行RMAN脚本权限验证
【7月更文挑战第24天】
以山向海
94 10
pbeskoyq7ffm4
|
3月前
|
Oracle 关系型数据库 数据库连接
初步了解Oracle DG
初步了解Oracle DG
pbeskoyq7ffm4
121 0
1941623231718325
|
6月前
|
Oracle 安全 关系型数据库
Oracle数据守卫（DG）：数据的“守护者”与“时光机”
【4月更文挑战第19天】Oracle Data Guard保障数据安全，通过实时维护备库实现故障切换，保证业务连续性。它使用日志传输和应用保持数据同步，如同“时光机”，借助闪回技术能恢复误操作数据。此外，它还提供数据压缩、加密和故障转移等功能，提升数据库安全性与性能。作为数据管理员，理解并善用Data Guard是确保企业数据安全的关键。
1941623231718325
94 0
靖宇ring
|
Oracle 关系型数据库 数据库
oracle导入时IMP-00010: 不是有效的导出文件, 头部验证失败
oracle导入时IMP-00010: 不是有效的导出文件, 头部验证失败
靖宇ring
226 1
翎野君
|
SQL Oracle 安全
Oracle DDL+DML+DCL实例
Oracle DDL+DML+DCL实例
翎野君
129 0
QGS
|
Oracle 关系型数据库 Linux
Centos7安装oracle客户端并验证oracle客户端
记Centos7安装oracle客户端并验证oracle客户端
QGS
858 0
Centos7安装oracle客户端并验证oracle客户端
YourBatman
|
Oracle Java 关系型数据库
【方向盘】Oracle Java SE Support Roadmap：支持JDK 8到2030年
Java的发展史，也伴随着JVM的变迁史
YourBatman
433 0
【方向盘】Oracle Java SE Support Roadmap：支持JDK 8到2030年
云内容小助手
|
存储 SQL Oracle
10 PostgreSQL 表级复制-物化视图篇, 支持异地,异构如 Oracle 到 pg 的物化视图|学习笔记
快速学习10 PostgreSQL 表级复制-物化视图篇,支持异地,异构如 Oracle 到 pg 的物化视图
云内容小助手
540 0
10 PostgreSQL 表级复制-物化视图篇, 支持异地,异构如 Oracle 到 pg 的物化视图|学习笔记
中间件兴趣圈
|
canal SQL Oracle
新的增量数据同步工具闪亮登场，完美支持Oracle增量同步
新的增量数据同步工具闪亮登场，完美支持Oracle增量同步
中间件兴趣圈
4570 0
新的增量数据同步工具闪亮登场，完美支持Oracle增量同步

热门文章

最新文章

  • 1
    Oracle运维笔记之IMPDP导入数据报错ORA-00943
  • 2
    备份及恢复Oracle 10g
  • 3
    oracle11g监听网卡总断（一）
  • 4
    ORACLE union order by
  • 5
    探索ORACLE之RMAN_07 参数文件丢失恢复
  • 6
    关于Oracle表及字段的注释 转
  • 7
    oracle10g Error in invoking target 'install' of makefile
  • 8
    oracle express 11g 创建表空间
  • 9
    误删Oracle数据文件的恢复方法
  • 10
    Oracle 用户、对象权限、系统权限
  • 1
    解决Toad for Oracle显示乱中文码问题
    90
  • 2
    【服务器】python通过JDBC连接到位于Linux远程服务器上的Oracle数据库
    82
  • 3
    零基础入门 Oracle数据库：轻松上手
    217
  • 4
    java操作多数据源将oracle数据同步达梦数据库
    280
  • 5
    SuSE linux server 11通过SAP来安装oracle11g
    103
  • 6
    oracle11g SAP测试机归档日志暴增排查（二）
    328
  • 7
    oracle11g SAP测试机归档日志暴增排查（一）
    79
  • 8
    修改oracle11g的awr快照参数
    48
  • 9
    发现oracle10gSYSAUX空间没有了进行处理
    41
  • 10
    在centos7虚拟机上安装docker oracle11g
    280

    • 相关课程

    更多
  • 如何在 PolarDB-X 中进行 Online DDL
  • 数据库备份DBS 产品功能介绍
  • 数据库及SQL/MySQL基础
  • Analytic DB MySql产品介绍

    • 相关电子书

    更多
  • PostgresChina2018_樊文凯_ORACLE数据库和应用异构迁移最佳实践
  • PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移
  • Oracle云上最佳实践

    • 相关实验场景

    更多
  • SQLServer数据库的创建
  • 使用DAS实现数据库自动SQL限流
  • PolarDB MySQL自动SQL限流
  • 如何开通SQL Server的专属集群

    • 推荐镜像

    更多
  • oracle
  • mysql
  • postgresql
    • 下一篇
    无影云桌面